package com.example.office.util.office;

import com.deepoove.poi.data.MiniTableRenderData;
import com.deepoove.poi.data.PictureRenderData;
import com.deepoove.poi.data.RowRenderData;
import com.deepoove.poi.data.TextRenderData;
import org.apache.poi.xssf.usermodel.*;
import org.jfree.chart.ChartUtils;
import org.jfree.chart.JFreeChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.awt.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;
import java.util.List;

/**
 * @ Author     ：jujh
 * @ Date       ：Created in 16:28 2018/12/27
 * @ Description：
 */
public class ExcelUtils {

    public static void main2(String[] args) throws Exception {
        String path = "D:\\temp\\excel\\test.xlsx";
        Map<String, Double> map = readChartXlsx(path);
        System.out.println(map);
    }

    public static void main(String[] args) throws Exception {
        String path = "D:\\temp\\excel\\test.xlsx";
        Map<String, Double> map = readChartXlsx(path);
        Font font = new Font("新宋体", Font.BOLD, 15);
        JFreeChart chart = CharReport_BZT.createPort("年龄区间结果", map, font);
        CharReport_ZZT.createPort("最受欢迎的女性年龄结果", map, "超神纪元", "数量单位（岁）", font);
        //将内存中的图片写到本地硬盘
        ChartUtils.saveChartAsJPEG(new File("d:/temp/aa.png"), chart, 600, 300);
        //将图片写到word中
        Map<String, Object> param = new HashMap<>();
        String templatePath = "D:\\temp\\template\\wordTemplate.docx";
        String outPath = "D:\\temp\\template\\result\\1.docx";
        param.put("title", new TextRenderData("整合table与图表数据到word中进行单元测试"));
        //表格数据
        String cellStr = readTableXlsx(path);
        String[] strArry = cellStr.split(";");
        String headers = strArry[0];
        //头部数据
        List<TextRenderData> heaserData = new ArrayList<>();
        List<String> headerList = Arrays.asList(headers.split(","));
        for (String str : headerList) {
            TextRenderData headerText = new TextRenderData();
            headerText.setText(str);
            heaserData.add(headerText);
        }
        //表格数据
        List<RowRenderData> rows = new ArrayList<>();
        for (int i = 1; i < strArry.length; i++) {
            RowRenderData rowRenderData = new RowRenderData();
            List<String> dataInfos = Arrays.asList(strArry[i].split(","));
            List<TextRenderData> textRenderDataList = new ArrayList<>();
            for (String str : dataInfos) {
                TextRenderData textRenderData = new TextRenderData();
                textRenderData.setText(str);
                textRenderDataList.add(textRenderData);
            }
            rowRenderData.setRowData(textRenderDataList);
            rows.add(rowRenderData);
        }
        param.put("detail", new MiniTableRenderData(new RowRenderData(heaserData), rows));
        param.put("image", new PictureRenderData(500, 500, "d:\\temp\\aa.png"));
        param.put("barImage", new PictureRenderData(500, 500, "d:\\temp\\bb.png"));
        WordUtil.template2Word(param, templatePath, outPath);

    }

    /**
     * 读取表格信息
     *
     * @param path
     * @throws Exception
     */
    private static String readTableXlsx(String path) throws Exception {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            System.out.println("获取表格==========");
            StringBuffer sb = new StringBuffer();
            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    //获取单元格格式
                    int cellNum = xssfRow.getPhysicalNumberOfCells();
                    for (int i = 0; i < cellNum; i++) {
                        XSSFCell cellInfoVal = xssfRow.getCell(i);
                        sb.append(cellInfoVal);
                        if (i != cellNum - 1) {
                            sb.append(",");
                        }
                    }
                    if (rowNum != xssfSheet.getLastRowNum()) {
                        sb.append(";");
                    }
                }
            }
            return sb.toString();
        }
        return null;
    }

    /**
     * 读取图片信息
     *
     * @param path
     * @throws Exception
     */
    private static byte[] readPictureXlsx(String path) throws Exception {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            System.out.println("获取图片=============");
            List<XSSFPictureData> pictureData = xssfWorkbook.getAllPictures();
            for (XSSFPictureData picture : pictureData) {
                System.out.println(picture.getData());
                System.out.println(picture.getMimeType());
                System.out.println(picture.getPictureType());
                return picture.getData();
            }
        }
        return null;
    }


    /**
     * 读取图表信息
     *
     * @param path
     * @throws Exception
     */
    private static Map<String, Double> readChartXlsx(String path) throws Exception {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            XSSFDrawing dw = xssfSheet.getDrawingPatriarch();
            List<XSSFChart> cs = dw.getCharts();
            for (XSSFChart c : cs) {
                //获取CTChart
                CTChart ctChart = c.getCTChart();
                CTPlotArea plotArea = ctChart.getPlotArea();
                //饼状图
                List<CTPieChart> pieCharts = plotArea.getPieChartList();
                if (pieCharts != null && !pieCharts.isEmpty()) {
                    for (CTPieChart ctPieChart : pieCharts) {
                        System.out.println("饼状图==================");
                        List<CTPieSer> ctBarSers = ctPieChart.getSerList();
                        for (CTPieSer ctPieSer : ctBarSers) {
                            CTAxDataSource ctAxDataSource = ctPieSer.getCat();
                            CTStrData ctStrData = ctAxDataSource.getStrRef().getStrCache();
                            //统计项
                            List<CTStrVal> ctStrVals = ctStrData.getPtList();
                            CTNumDataSource ctNumDataSource = ctPieSer.getVal();
                            List<CTNumVal> ctNumVals = ctNumDataSource.getNumRef().getNumCache().getPtList();
                            //封装返回饼状图数据
                            Map<String, Double> map = returnPieData(ctStrVals, ctNumVals);
                            return map;
                        }
                    }
                }

                //折线图
                List<CTLineChart> lineCharts = plotArea.getLineChartList();
                if (lineCharts != null && !lineCharts.isEmpty()) {
                    for (CTLineChart ctLineChart : lineCharts) {
//                        System.out.println("折线图==================");
//                        List<CTLineSer> ctLineSers = ctLineChart.getSerList();
//                        for (CTLineSer ctLineSer : ctLineSers) {
//                            CTAxDataSource ctAxDataSource = ctLineSer.getCat();
//                            CTMultiLvlStrData ctMultiLvlStrData = ctAxDataSource.getMultiLvlStrRef().getMultiLvlStrCache();
//                            List<CTLvl> ctLvls = ctMultiLvlStrData.getLvlList();
//                            for (CTLvl ctLvl : ctLvls) {
//                                List<CTStrVal> ctStrVals = ctLvl.getPtList();
//                                //存储统计数据
//                                for (CTStrVal ctStrVal : ctStrVals) {
//                                    System.out.println(ctStrVal.getV());
//                                }
//                            }
//                            CTNumDataSource ctNumDataSource = ctLineSer.getVal();
//                            List<CTNumVal> ctNumVals = ctNumDataSource.getNumRef().getNumCache().getPtList();
//                            //存储统计项数据
//                            for (CTNumVal ctNumVal : ctNumVals) {
//                                System.out.println(ctNumVal.getV());
//                            }
//                        }
                    }
                }
                //柱状图
                List<CTBarChart> barCharts = plotArea.getBarChartList();
                if (barCharts != null && !barCharts.isEmpty()) {
                    for (CTBarChart ctBarChart : barCharts) {
                        System.out.println("柱状图==================");
                        List<CTBarSer> ctBarSers = ctBarChart.getSerList();
                        for (CTBarSer ctBarSer : ctBarSers) {
                            CTAxDataSource ctAxDataSource = ctBarSer.getCat();
                            CTStrData ctStrData = ctAxDataSource.getStrRef().getStrCache();
                            //数据项
                            List<CTStrVal> ctStrVals = ctStrData.getPtList();
                            CTNumDataSource ctNumDataSource = ctBarSer.getVal();
                            //统计值
                            List<CTNumVal> ctNumVals = ctNumDataSource.getNumRef().getNumCache().getPtList(); //封装返回饼状图数据
                            Map<String, Double> map = returnPieData(ctStrVals, ctNumVals);
                            return map;

                        }
                    }
                }
                //散点图
                List<CTScatterChart> scatterCharts = plotArea.getScatterChartList();
                if (scatterCharts != null && !scatterCharts.isEmpty()) {
                    for (CTScatterChart ctScatterChart : scatterCharts) {
                        //System.out.println("散点图==================");
                    }
                }

            }
        }
        return null;
    }

    /**
     * 统计图表数据返回map数据
     *
     * @param ctStrVals
     * @param ctNumVals
     * @return
     */
    private static LinkedHashMap<String, Double> returnPieData(List<CTStrVal> ctStrVals, List<CTNumVal> ctNumVals) {
        LinkedHashMap<String, Double> map = new LinkedHashMap<>();
        if (ctStrVals != null && !ctStrVals.isEmpty()) {
            for (int i = 0; i < ctStrVals.size(); i++) {
                String item = ctStrVals.get(i).getV();
                String values = ctNumVals.get(i).getV();
                map.put(item, Double.valueOf(values));
            }
        }
        return map;
    }
}

